﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace WaveMaster.Holidays.Club.SQL
{
    public class TicketDeductionSQL
    {
         public static string GetAllTicketDeduction()
        {
            string sql = "select * from ticket_deduction order by m_transactionDate DESC LIMIT 100";
            return sql;
        }
        public static string GetTicketDeductionByID()
        {
            string sql = "select * from ticket_deduction p, travel_agent a WHERE m_deductID = ?m_deductID and p.m_travelAgentId=a.m_travelAgentId";
            return sql;
        }
        public static string GetAllTicketDeductionByAgent()
        {
            string sql = "select * from ticket_deduction where m_travelAgentId=?m_travelAgentId";
            return sql;
        }
        public static string GetCountTicketDeductionByAgent()
        {
            string sql = "select count(*) from ticket_deduction where m_travelAgentId=?m_travelAgentId";
            return sql;
        }
        public static string SearchTicketDeductionByCodeName(string p)
        {
            string sql = @"select p.* from ticket_deduction p, travel_agent a WHERE p.m_travelAgentId = a.m_travelAgentId and concat(p.m_code,
            p.m_transactionDate,a.m_code,a.m_name) like '%" + p + "%'";
            return sql;
        }

        public static string SearchTicketDeductionByCodeNameTravelAgent(string p)
        {
            string sql = @"select p.* from ticket_deduction p, travel_agent a WHERE p.m_travelAgentId = a.m_travelAgentId and concat(p.m_code,
            p.m_transactionDate,a.m_code,a.m_name) like '%" + p + "%' and p.m_travelAgentId=?m_travelAgentId";
            return sql;
        }
        public static string DeleteTicketDeductionByID()
        {
            return @"DELETE FROM ticket_deduction
      WHERE m_deductID = ?m_deductID";
        }
        public static string InsertTicketDeduction()
        {
            string sql = @"INSERT INTO ticket_deduction
           (m_code
            ,m_transactionDate
            ,m_travelAgentId
            ,m_quantity
            ,m_ticketType
            ,m_remark
            ,m_cretnDate            
            ,m_cretnBy
            ,m_modfctnDate
            ,m_modfctnBy
            )
     VALUES
           (
            ?m_code
            ,?m_transactionDate
            ,?m_travelAgentId
            ,?m_quantity
            ,?m_ticketType
            ,?m_remark
            ,?m_cretnDate
            ,?m_cretnBy
            ,?m_modfctnDate
            ,?m_modfctnBy
            )";
            return sql;
        }
        public static string UpdateTicketDeduction()
        {
            string sql = @"UPDATE ticket_deduction
            SET m_code =?m_code
            ,m_transactionDate=?m_transactionDate
            ,m_travelAgentId=?m_travelAgentId
            ,m_quantity=?m_quantity
            ,m_ticketType=?m_ticketType
            ,m_remark = ?m_remark
            ,m_modfctnDate=?m_modfctnDate
            ,m_modfctnBy=?m_modfctnBy
 WHERE m_deductID = ?m_deductID";
            return sql;
        }

//        public static string GetTotalPurchaseByAgentID()
//        {
//            string sql = @"select sum(m_quantity) from ticket_deduction 
//                    where m_travelAgentId=?m_travelAgentId and m_ticketType=?m_ticketType";
//            return sql;
//        }

        public static string ReportTicketDeductionByDateAndTravelAgent()
        {
            string sql = @"select * from ticket_deduction p, travel_agent ta WHERE ta.m_travelAgentId = p.m_travelAgentId and
                        m_transactionDate between ?fromdate and ?todate ";
            return sql;
        }
    }
}
